package com.example.javatool.recallfile;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.platform.commons.util.StringUtils;

import java.io.*;
import java.util.*;

public class ExcelDataProcessor2 {

    /**
     * 处理 CSV 文件并将结果保存到指定文档
     *
     * @param csvFilePath    CSV 文件路径
     * @param matchFilePath  用于匹配的文档路径
     * @param outputFilePath 输出结果的文档路径
     * @throws IOException 当文件操作出错时抛出
     */
    public static void processCsv(String csvFilePath, String matchFilePath, String outputFilePath, String outputFileTxtPath) throws IOException {
        Set<String> fileNameSet = getFileNamesFromDirectory(matchFilePath);
        // 处理 CSV 文件
        List<String> results = processExcelFile(csvFilePath, fileNameSet, outputFileTxtPath);

        // 将结果保存到文档
        saveResultsToFile(results, outputFilePath);
    }

    /**
     * 读取用于匹配的文档数据
     *
     * @param matchFilePath 用于匹配的文档路径
     * @return 包含匹配数据的 Map，键为第二列数据，值为第一列数据
     * @throws IOException 当文件操作出错时抛出
     */
    private static Map<String, String> readMatchFile(String matchFilePath) throws IOException {
        Map<String, String> matchMap = new HashMap<>();
        try (BufferedReader reader = new BufferedReader(new FileReader(matchFilePath))) {
            String line;
            while ((line = reader.readLine()) != null) {
                String[] parts = line.split(" ");
                if (parts.length >= 2) {
                    matchMap.put(parts[0], parts[1]);
                }
            }
        }
        return matchMap;
    }

    /**
     * 处理 Excel 文件
     *
     * @param excelFilePath Excel 文件路径
     * @param fileNameSet   包含匹配数据的 Map
     * @return 处理结果列表
     * @throws IOException 当文件操作出错时抛出
     */
    private static List<String> processExcelFile(String excelFilePath, Set<String> fileNameSet, String outputFileTxtPath) throws IOException {
        List<String> results = new ArrayList<>();
        int linecount = 0;

        // 存储所有对比结果（A数据，B数据第一列，相似度）
        List<StringSimilarityAnalyzer.SimilarityResult> result2s = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fileInputStream)) {
            Sheet sheet = workbook.getSheet("转受让订单文档"); // 获取第一个 sheet
            boolean isHeader = true;
            for (Row row : sheet) {
                linecount++;
                if (isHeader) {
                    isHeader = false;
                    System.out.println("调过:" + linecount);
                    continue; // 跳过表头
                }
                // 确保行有足够的单元格
                if (row.getLastCellNum() < 20) {
                    System.out.println("调过:" + linecount);
                    continue; // 跳过无效行
                }
                String productName = getCellValueAsString(row.getCell(0));
                String transCustomerName = getCellValueAsString(row.getCell(3));
                String acceptCustomerName = getCellValueAsString(row.getCell(5));
                String docName = getCellValueAsString(row.getCell(12));
                String userName = getCellValueAsString(row.getCell(9));
                String md5 = getCellValueAsString(row.getCell(23));
                if(   StringUtils.isNotBlank(md5)){
                    continue;
                }
                String tempName = String.join("-",productName,transCustomerName,acceptCustomerName,docName);
                for (String fileName : fileNameSet) {
                    //文件不属于上传人或者已有md5，跳过
                    if (//!fileName.contains(userName)||
                            fileName.contains("过滤")) {
                        continue;
                    }
                    String[] split = fileName.replace(".pdf","").split("-");
                    String fileUserName=split[0];
                    String fileCustomerName = split[split.length - 1];
                    //文件包含了客户名称和文档名称，直接返回100
                    if ((fileName.contains(transCustomerName) && fileName.contains(docName)))
                     {
                        result2s.add(new StringSimilarityAnalyzer.SimilarityResult(linecount,linecount+"-"+ tempName, fileName, 100));
                        continue;
                    }
                    if (fileName.contains(transCustomerName) || tempName.contains(fileCustomerName)
                            || (tempName.contains(fileCustomerName) && tempName.contains(fileUserName))) {
                        result2s.add(new StringSimilarityAnalyzer.SimilarityResult(linecount ,linecount+"-"+tempName, fileName, 95));
                        continue;
                    }
                    double similarity = StringSimilarityAnalyzer.callSimilarityApi3(tempName, fileName.replace(".pdf", "")); // 调用相似度计算方法
                    result2s.add(new StringSimilarityAnalyzer.SimilarityResult(linecount,linecount+"-"+ tempName, fileName, similarity));
                    if (similarity == 100) {
                        break;
                    }
                }
                System.out.println("处理:" + linecount);

            }
            // 过滤相似度小于50的数据
            result2s = result2s.stream()
                    .filter(result -> result.similarity >= 40)
//                    .filter(result->result.aValue.contains("陈珏溢"))
                    .collect(java.util.stream.Collectors.toList());
            // 按相似度从高到低排序
            result2s.sort(Comparator.comparingDouble((StringSimilarityAnalyzer.SimilarityResult r) -> -r.similarity)
                    .thenComparing(r -> r.line));

            // 保存结果到文件
            StringSimilarityAnalyzer.saveResultsToFile(result2s, outputFileTxtPath);
        }
        return results;
    }

    /**
     * 获取单元格的值并转换为字符串
     *
     * @param cell 单元格对象
     * @return 单元格的值的字符串表示
     */
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try{
                    return cell.getStringCellValue();
                }catch (Exception e){
                 return "";
                }

            default:
                return "";
        }
    }

    /**
     * 从指定目录获取所有文件的名称
     *
     * @param directoryPath 指定目录的路径
     * @return 包含所有文件名称的 Set
     */
    private static Set<String> getFileNamesFromDirectory(String directoryPath) {
        Set<String> fileNameSet = new HashSet<>();
        File directory = new File(directoryPath);
        if (directory.exists() && directory.isDirectory()) {
            File[] files = directory.listFiles();
            if (files != null) {
                for (File file : files) {
                    if (file.isFile()) {
                        fileNameSet.add(file.getName());
                    }
                }
            }
        }
        return fileNameSet;
    }

    /**
     * 将处理结果保存到文档
     *
     * @param results        处理结果列表
     * @param outputFilePath 输出结果的文档路径
     * @throws IOException 当文件操作出错时抛出
     */
    private static void saveResultsToFile(List<String> results, String outputFilePath) throws IOException {
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fileOut = new FileOutputStream(outputFilePath)) {
            Sheet sheet = workbook.createSheet("Results");

            int rowNum = 0;
            for (String result : results) {
                Row row = sheet.createRow(rowNum++);
                String[] parts = result.split(" ");
                for (int i = 0; i < parts.length; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(parts[i]);
                }
            }

            // 自动调整列宽
            for (int i = 0; i < 2; i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(fileOut);
        }
    }

    public static void main(String[] args) {
        String csvFilePath = "D:\\email\\运营文件.xlsx";
        String matchFilePath = "D:\\email\\附件";
        String outputFilePath = "D:\\email\\csv_match.xlsx";
        String outputFileTxtPath = "D:\\email\\similar.txt";
        try {
            processCsv(csvFilePath, matchFilePath, outputFilePath, outputFileTxtPath);
            System.out.println("处理完成，结果已保存到 " + outputFilePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
